﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Data.OleDb;
using System.Data;
using sWarranty;
using System.Web.Configuration;
using Oracle.DataAccess.Client;
using System.Text;
using System.IO;
using System.Web.UI;

namespace LCUV
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {

        [WebMethod]
        public List<TB_CM_INCENTIVE_DETAIL> listModelByScheme(string schemeID)
        {
            Entities db = new Entities();
            List<TB_CM_INCENTIVE_DETAIL> list = new List<TB_CM_INCENTIVE_DETAIL>();
            var sch = from t in db.TB_CM_INCENTIVE_DETAIL
                      where t.SCHEME_ID == schemeID
                      orderby t.CREATE_DATE descending, t.MODEL
                      select t;
            if (sch.Count() == 0)
                return null;
            else
            {
                return sch.ToList(); //DBAction.LINQToDataTable(sch);
            }

        }

        [WebMethod]
        public string FindBySchemeAndModel(string schemeID, string model)
        {
            Entities db = new Entities();
            string st = "";
            var sch = from t in db.TB_CM_INCENTIVE_DETAIL
                      where t.SCHEME_ID == schemeID && t.MODEL.ToLower().Contains(model.Trim().ToLower())
                      orderby t.CREATE_DATE descending, t.MODEL
                      select t;
            if (sch.Count() == 0)
                return "";
            else
            {
                st += "<table id='newspaper-b' class='uiroi'><thead><tr><th scope='col'>#</th><th scope='col'>SCHEME_ID</th><th scope='col'>MODEL</th><th scope='col'>UNIT</th><th scope='col'>POINT</th><th scope='col'>AMT</th><th scope='col'>REGION</th></tr></thead><tbody>";
                //<tr><td colspan='6'>Model: <input type='text' value='All' style='width:200px;' onclick=\"findModel('scheme','model')\" /></td></tr>
                int dem = 1;

                foreach (var item in sch)
                {
                    st += "<tr style='text-align:center'>";
                    st += "<td>" + dem + " </td>";
                    st += "<td>" + item.SCHEME_ID + " </td>";
                    st += "<td>" + item.MODEL + " </td>";
                    st += "<td>" + item.UNIT + " </td>";
                    st += "<td>" + item.POINT + " </td>";
                    st += "<td>" + item.AMT + " </td>";
                    st += "<td>" + DBAction.GetRegionByID(item.REGION) + " </td>";
                    st += "</tr>";
                    dem++;
                }
                st += "</tbody>";
                st += "</table>";
                return st;
            }
        }
        protected string GetLocation(int districtID)
        {
            try
            {
                Entities db = new Entities();
                var di = from t in db.TB_CM_DISTRICT
                         where t.ID == districtID
                         select t;
                int _ProID = di.Single().PROVINCE_ID;
                var pro = from t in db.TB_CM_PROVINCE
                          where t.ID == _ProID
                          select t;
                return di.Single().NAME + ", " + pro.Single().NAME;
            }
            catch (Exception)
            {
                return "Error while get location";
            }

        }

        [WebMethod]
        public string setReject(string sn, string _curUser)
        {
            try
            {
                string error = "";
                string stx = "select pk_som_mst.FN_MNL_ACT_PND_REJ('" + sn + "','','" + _curUser + "') from dual";

                OracleCommand cmd = DBAction.con.CreateCommand();
                OracleParameter param = new OracleParameter();
                cmd.CommandText = stx;
                cmd.CommandType = CommandType.Text;
                var x = cmd.ExecuteScalar();
                error = x.ToString();
                return error;
            }
            catch (Exception ee)
            {
                return ee.Message;
            }
        }

        [WebMethod]
        public string findPickOrder(string p_billto_code, string p_model, string p_suffix, string p_serial_no, string p_box_no, string p_prod_date, string p_act_user)
        {
            string box = null;
            string st = "";
            string sf = "%";//sf for PR_PICK_TRC(find pick order)
            string sf2 = null; //sf for FN_MNL_ACT_SO_SAV(confirm)
            p_prod_date = p_prod_date.Replace("-", "");
            if (!string.IsNullOrEmpty(p_box_no))
                box = p_box_no;
            if (!string.IsNullOrEmpty(p_suffix))
            {
                sf = p_suffix;
                sf2 = p_suffix;
            }
            try
            {
                DataTable dt = new DataTable();
                dt = DBAction.runStore("PK_SOM_MST.PR_PICK_TRC", "uiroi", "p_billto_code", p_billto_code,
                    "p_model", p_model, "p_suffix", sf,
                    null, null, null, null, null, null, null, null, null, null, null, null
                    , null, null, null, null, null, null, null, null, null, null, null, null);
                try
                {
                    if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["Error"].ToString()))
                    {
                        return (dt.Rows[0]["Error"].ToString());
                    }
                }
                catch (Exception)
                {
                    //Column 'Error' does not belong to table <-- ko co' loi j va co' data , thi ko lam j
                }
                int i = 1;
                if (dt.Rows.Count > 0)
                {
                    st += "<table class='datatablecss'><thead><tr><th>#</th><th>BILLTO_NAME</th><th>PRODUCT</th><th>MODEL</th><th>SUFFIX</th><th>SHIPTO_NAME</th><th>INVOICE_NO</th><th>INVOICE_DATE</th><th>DELIVERY_DATE</th><th>ORDER_QTY</th><th>RELEASE_QTY</th><th>PICK_NO</th><th></th></tr></thead><tbody>";
                    foreach (DataRow item in dt.Rows)
                    {
                        st += "<tr>";
                        st += "   <td>" + i.ToString() + "</td>";
                        st += "   <td>" + item["BILLTO_NAME"].ToString() + "</td>";
                        st += "   <td>" + item["PRODUCT"].ToString() + "</td>";
                        st += "   <td>" + item["MODEL"].ToString() + "</td>";
                        st += "   <td>" + item["SUFFIX"].ToString() + "</td>";
                        st += "   <td>" + item["SHIPTO_NAME"].ToString() + "</td>";
                        st += "   <td>" + item["INVOICE_NO"].ToString() + "</td>";
                        if (!string.IsNullOrEmpty(item["INVOICE_DATE"].ToString()))
                            st += "   <td>" + Convert.ToDateTime(item["INVOICE_DATE"].ToString()).ToString("yyyy-MMM-dd") + "</td>";
                        else
                            st += "   <td>" + " - " + "</td>";
                        if (!string.IsNullOrEmpty(item["DELIVERY_DATE"].ToString()))
                            st += "   <td>" + Convert.ToDateTime(item["DELIVERY_DATE"].ToString()).ToString("yyyy-MMM-dd") + "</td>";
                        else
                            st += "   <td>" + " - " + "</td>";
                        st += "   <td>" + item["ORDER_QTY"].ToString() + "</td>";
                        st += "   <td>" + item["RELEASE_QTY"].ToString() + "</td>";
                        st += "   <td style='color: red;'>" + item["PICK_NO"].ToString() + "</td>";
                        if (!string.IsNullOrEmpty(item["SUFFIX"].ToString()))
                        {
                            sf2 = item["SUFFIX"].ToString();
                        }
                        st += "    <td><input type='button' id='btnOrder" + i.ToString() + "' value='Confirm' onclick=\"doConfirm('" + p_billto_code + "','" + item["PICK_NO"].ToString() + "','" + p_serial_no + "','" + box + "','" + item["MODEL"].ToString() + "','" + sf2 + "','" + p_prod_date + "','" + p_act_user + "')\" /></td>";
                        st += "</tr>";
                        i++;
                    }
                    st += "</tbody></table>";
                }
                else
                {
                    st = "No Pick_Order of Model: <b>" + p_model + "</b>, Suffix: <b>" + sf2 + "</b>, BillToCode: <b>" + p_billto_code + "</b>";
                }
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return (ee.InnerException.Message);
                else
                    return (ee.Message);
            }
            return st;
        }

        [WebMethod]
        public string doConfirm(string p_pick_no, string p_serial_no, string p_box_no, string p_model, string p_suffix,
            string p_prod_date, string p_act_user)
        {
            try
            {
                string error = "";
                string stx = "select pk_som_mst.FN_MNL_ACT_SO_SAV('" + p_pick_no + "','" + p_serial_no + "','" + p_box_no + "','" + p_model + "','" + p_suffix + "','" + p_prod_date + "','" + p_act_user + "') from dual";

                OracleCommand cmd = DBAction.con.CreateCommand();
                OracleParameter param = new OracleParameter();
                cmd.CommandText = stx;
                cmd.CommandType = CommandType.Text;
                var x = cmd.ExecuteScalar();
                error = x.ToString();

                ////log
                //if (DBAction.LogUser == true)
                //{
                //    Entities db = new Entities();
                //    TB_CM_SYS_LOG lg = new TB_CM_SYS_LOG();
                //    string seq_key = Guid.NewGuid().ToString();
                //    string tit = "Close Incentive #1: Create";
                //    lg = new TB_CM_SYS_LOG();
                //    lg.ID = String.Format("{0:u}", DateTime.Now);
                //    lg.ENTRY_USER_ID = p_act_user;
                //    lg.TITLE = tit;
                //    lg.SEQ_KEY = seq_key;
                //    lg.ACTION1 = "select pk_som_mst.FN_MNL_ACT_SO_SAV('" + p_pick_no + "','" + p_serial_no + "','" + p_box_no + "','" + p_model + "','" + p_suffix + "','" + p_prod_date + "','" + p_act_user + "') from dual";
                //    lg.REMARK = error;
                //    lg.ENTRY_DATE_TIME = DateTime.Now;
                //    db.TB_CM_SYS_LOG.AddObject(lg);
                //}

                return error;
            }
            catch (Exception ee)
            {
                return ee.Message;
            }
        }

        [WebMethod]
        public string changePass(string empNo, string newpass)
        {
            try
            {
                Entities db = new Entities();
                var u = db.TB_USER.Single(t => t.EMPNO == empNo);
                u.PASS = EncDec.Encrypt(newpass.Trim(), "");
                u.UPDATE_DATE_TIME = DateTime.Now;
                u.UPDATE_USER_NAME = empNo;
                db.SaveChanges();

                //log
                if (DBAction.LogUser == true)
                {
                    TB_CM_SYS_LOG lg = new TB_CM_SYS_LOG();
                    string seq_key = Guid.NewGuid().ToString();
                    string tit = empNo + " Changed pass";
                    lg = new TB_CM_SYS_LOG();
                    lg.ID = String.Format("{0:u}", DateTime.Now);
                    lg.ENTRY_USER_ID = empNo;
                    lg.TITLE = tit;
                    lg.SEQ_KEY = seq_key;
                    lg.ACTION1 = "User Change Password";
                    lg.ENTRY_DATE_TIME = DateTime.Now;
                    db.TB_CM_SYS_LOG.AddObject(lg);
                }
                return "DONE";
            }
            catch (Exception ee)
            {
                return "FAIL: " + ee.Message;
            }
        }


        [WebMethod]
        public string findModel(string md)
        {
            string st = "";
            try
            {
                DataTable dt = new DataTable();
                dt = DBAction.runStore("PK_SOM_MST.PR_PROD_MODEL_TRC", "uiroi", "p_model", md,
                    null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
                    , null, null, null, null, null, null, null, null, null, null, null, null);
                try
                {
                    if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["Error"].ToString()))
                    {
                        return (dt.Rows[0]["Error"].ToString());
                    }
                }
                catch (Exception)
                {
                    //Column 'Error' does not belong to table <-- ko co' loi j va co' data , thi ko lam j
                }
                int i = 1;
                int total = dt.Rows.Count;
                if (dt.Rows.Count > 0)
                {
                    st += "<table class='datatablecss'><thead><tr><th>#</th><th>MODEL</th><th>SAL_MODEL</th><th>SAL_SUFFIX</th><th></th></tr></thead><tbody>";
                    foreach (DataRow item in dt.Rows)
                    {
                        st += "<tr>";
                        st += "   <td>" + i.ToString() + "</td>";
                        st += "   <td>" + item["PROD_MODEL_SUFFIX"].ToString() + "</td>";
                        st += "   <td>" + item["SALES_MODEL"].ToString() + "</td>";
                        st += "   <td>" + item["SALES_SUFFIX"].ToString() + "</td>";
                        st += "    <td><input type='button' id='btnSelect" + i.ToString() + "' value='Select' onclick=\"setSelectOnPopup('" + total + "','" + i.ToString() + "','" + item["PROD_MODEL_SUFFIX"].ToString() + "','" + item["SALES_MODEL"].ToString() + "','" + item["SALES_SUFFIX"].ToString() + "')\" /><img id='imgSelect" + i.ToString() + "' src='../IMAGES/icons/fugue/tick-circle.png' style='display:none;' /></td>";
                        st += "</tr>";
                        i++;
                    }
                    st += "</tbody></table>";
                }
                else
                {
                    st = "No Model Found";
                }
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return (ee.InnerException.Message);
                else
                    return (ee.Message);
            }
            return st;
        }

        [WebMethod]
        public string findBill(string bill)
        {
            string st = "";
            try
            {
                DataTable dt = new DataTable();
                dt = DBAction.runStore("PK_SOM_MST.PR_BILLTO_TRC", "uiroi", "p_bill_shop_name", bill,
                    null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
                    , null, null, null, null, null, null, null, null, null, null, null, null);
                try
                {
                    if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["Error"].ToString()))
                    {
                        return (dt.Rows[0]["Error"].ToString());
                    }
                }
                catch (Exception)
                {
                    //Column 'Error' does not belong to table <-- ko co' loi j va co' data , thi ko lam j
                }
                int i = 1;
                int total = dt.Rows.Count;
                if (dt.Rows.Count > 0)
                {
                    st += "<table class='datatablecss'><thead><tr><th>#</th><th>BILLTO_CODE</th><th>BILLTO_NAME</th><th>REGION</th><th></th></tr></thead><tbody>";
                    foreach (DataRow item in dt.Rows)
                    {
                        st += "<tr>";
                        st += "   <td>" + i.ToString() + "</td>";
                        st += "   <td>" + item["BILLTO_CODE"].ToString() + "</td>";
                        st += "   <td>" + item["BILLTO_NAME"].ToString() + "</td>";
                        st += "   <td>" + item["REGION"].ToString() + "</td>";
                        st += "    <td><input type='button' id='btnSelect" + i.ToString() + "' value='Select' onclick=\"setSelectOnPopup2('" + total + "','" + i.ToString() + "','" + item["BILLTO_CODE"].ToString() + "')\" /><img id='imgSelect" + i.ToString() + "' src='../IMAGES/icons/fugue/tick-circle.png' style='display:none;' /></td>";
                        st += "</tr>";
                        i++;
                    }
                    st += "</tbody></table>";
                }
                else
                {
                    st = "No Bill Found";
                }
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return (ee.InnerException.Message);
                else
                    return (ee.Message);
            }
            return st;
        }

        [WebMethod]
        public string closingCreate(string p_yyyymm, string p_s_date, string p_e_date, string p_scheme_id, string p_user, string p_purpose)
        {
            try
            {
                string error = "";
                string stx = "select pk_som_mst.FN_INCT_CLOSING_CRT('" + p_yyyymm + "','" + p_s_date.Replace("-", "") + "','" + p_e_date.Replace("-", "") + "','" + p_scheme_id + "','" + p_user + "','" + p_purpose + "') from dual";
                OracleCommand cmd = DBAction.con.CreateCommand();
                OracleParameter param = new OracleParameter();
                cmd.CommandText = stx;
                cmd.CommandType = CommandType.Text;
                var x = cmd.ExecuteScalar();
                error = x.ToString();

                //log
                if (DBAction.LogUser == true)
                {
                    Entities db = new Entities();
                    TB_CM_SYS_LOG lg = new TB_CM_SYS_LOG();
                    string seq_key = Guid.NewGuid().ToString();
                    string tit = "Close Incentive #1: Create";
                    lg = new TB_CM_SYS_LOG();
                    lg.ID = String.Format("{0:u}", DateTime.Now);
                    lg.ENTRY_USER_ID = p_user;
                    lg.TITLE = tit;
                    lg.SEQ_KEY = seq_key;
                    lg.ACTION1 = "select pk_som_mst.FN_INCT_CLOSING_CRT('" + p_yyyymm + "','" + p_s_date.Replace("-", "") + "','" + p_e_date.Replace("-", "") + "','" + p_scheme_id + "','" + p_user + "','" + p_purpose + "') from dual";
                    lg.REMARK = error;
                    lg.ENTRY_DATE_TIME = DateTime.Now;
                    db.TB_CM_SYS_LOG.AddObject(lg);
                }
                return error;
            }
            catch (Exception ee)
            {
                return ee.Message;
            }
        }

        [WebMethod]
        public string confirmCloseScheme(string v_period, string p_scheme_id, string p_user)
        {
            try
            {
                string error = "";
                string stx = "select pk_som_mst.FN_INCT_CLOSING('" + v_period + "','" + p_scheme_id + "','" + p_user + "') from dual";
                OracleCommand cmd = DBAction.con.CreateCommand();
                OracleParameter param = new OracleParameter();
                cmd.CommandText = stx;
                cmd.CommandType = CommandType.Text;
                var x = cmd.ExecuteScalar();
                error = x.ToString();

                //log
                if (DBAction.LogUser == true)
                {
                    Entities db = new Entities();
                    TB_CM_SYS_LOG lg = new TB_CM_SYS_LOG();
                    string seq_key = Guid.NewGuid().ToString();
                    string tit = "Close Incentive #2: Confirm";
                    lg = new TB_CM_SYS_LOG();
                    lg.ID = String.Format("{0:u}", DateTime.Now);
                    lg.ENTRY_USER_ID = p_user;
                    lg.TITLE = tit;
                    lg.SEQ_KEY = seq_key;
                    lg.ACTION1 = "select pk_som_mst.FN_INCT_CLOSING('" + v_period + "','" + p_scheme_id + "','" + p_user + "') from dual";
                    lg.REMARK = error;
                    lg.ENTRY_DATE_TIME = DateTime.Now;
                    db.TB_CM_SYS_LOG.AddObject(lg);
                }
                return error;
            }
            catch (Exception ee)
            {
                return ee.Message;
            }
        }


        [WebMethod]
        public string loadCloseScheme(string p_scheme_id)
        {
            string st = "";
            try
            {
                DataTable dt = new DataTable();
                Entities db = new Entities();
                var cs = from t in db.TB_CM_INCENTIVE_CLOSE_MST
                         where t.SCHEME_ID == p_scheme_id && t.CLOSE_FLAG == "N"
                         select t;
                int total = dt.Rows.Count;
                int i = 1;
                if (cs.Count() > 0)
                {
                    st += "<table class='datatablecss'><thead><tr><th>#</th><th>PERIOD</th><th>START</th><th>END</th><th>PURPOSE</th><th></th></tr></thead><tbody>";
                    foreach (var item in cs)
                    {
                        st += "<tr>";
                        st += "   <td>" + i.ToString() + "</td>";
                        st += "   <td>" + item.CLOSE_PERIOD + "</td>";
                        st += "   <td>" + item.START_DATE + "</td>";
                        st += "   <td>" + item.END_DATE + "</td>";
                        st += "   <td>" + item.PURPOSE + "</td>";
                        st += "   <td><input type='button' id='btnSchemeCloseConfirm" + item.CLOSE_PERIOD + "' value='Confirm' onclick=\"confirmCloseScheme('" + item.CLOSE_PERIOD + "')\" /></td>";
                        st += "</tr>";
                        i++;
                    }
                    st += "</tbody></table>";
                }
                else
                {
                    st = "No Period Found. ";
                }
                return st;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return (ee.InnerException.Message);
                else
                    return (ee.Message);
            }

        }

        [WebMethod]
        public string CheckLog(string lastLog)
        {
            string st = "";
            DateTime _lastLog = Convert.ToDateTime(lastLog);
            try
            {
                DataTable dt = new DataTable();
                Entities db = new Entities();
                var cs = (from t in db.TB_CM_SYS_LOG
                          where t.ENTRY_DATE_TIME > _lastLog
                          orderby t.ENTRY_DATE_TIME ascending
                          select t).Take(1);
                if (cs.Count() > 0)
                {
                    if (!cs.Single().TITLE.Contains("Login"))//ko hien thi notify when Login
                        st = "<b>" + cs.Single().TITLE + "</b> - " + cs.Single().ENTRY_DATE_TIME.Value.ToShortTimeString()
                            + "|" + cs.Single().ENTRY_DATE_TIME.ToString();
                }
                else
                {
                    st = "";
                }
                return st;
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return (ee.InnerException.Message);
                else
                    return (ee.Message);
            }

        }

        [WebMethod]
        public string showClosingInfo(string schemeID)
        {
            Entities db = new Entities();
            var sche = from t in db.TB_CM_INCENTIVE_CLOSE_MST
                       where t.SCHEME_ID == schemeID
                       select t;
            if (sche.Count() > 0)
            {
                string st = "";
                st += sche.Single().CLOSE_PERIOD + "|" + sche.Single().PURPOSE + "|";
                if (!string.IsNullOrEmpty(sche.Single().CLOSE_USER))
                    st += sche.Single().CLOSE_USER;
                return st;
            }
            else
                return "No result.";
        }

        [WebMethod]
        public string fillSO(string p_shop_code, string p_shop_name, string p_f_dt, string p_t_dt, string p_vrf_flag,
            string p_cfm_flag, string p_user_region, string p_region_id)
        {
            string st = "";
            p_f_dt = p_f_dt.Replace("/", "");
            p_t_dt = p_t_dt.Replace("/", "");
            if (string.IsNullOrEmpty(p_f_dt))
                p_f_dt = "%";
            if (string.IsNullOrEmpty(p_t_dt))
                p_t_dt = "%";
            if (p_region_id == "00")
                p_region_id = "%";
            try
            {
                DataTable dt = new DataTable();
                dt = DBAction.runStore("PK_SOM_MST.PR_SO_DEALER_INQ", "uiroi", "p_shop_code", p_shop_code,
                    "p_shop_name", p_shop_name, "p_f_dt", p_f_dt, "p_t_dt", p_t_dt, "p_vrf_flag", p_vrf_flag,
                    "p_cfm_flag", p_cfm_flag, "p_user_region", p_user_region, "p_region_id", p_region_id,
                    null, null, null, null, null, null, null, null, null, null, null, null, null, null);
                try
                {
                    if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0]["Error"].ToString()))
                    {
                        return (dt.Rows[0]["Error"].ToString());
                    }
                }
                catch (Exception)
                {
                    //Column 'Error' does not belong to table <-- ko co' loi j va co' data , thi ko lam j
                }
                int i = 1;
                int total = dt.Rows.Count;
                if (dt.Rows.Count > 0)
                {
                    st += "<table class='datatablecss'><thead><tr><th>#</th><th>EDI_NO</th><th>SERIAL_NO</th><th>MODEL</th><th>SUFFIX</th><th>SELLIN_DATE</th><th>SELLOUT_DATE</th><th>VRF_FLAG</th><th>CFM_FLAG</th><th>CFM_USER</th><th>USER_CELL</th><th>CFM_COMMENT</th><th>SHOP_CODE</th><th>SHOP_NAME</th><th>SHOP_CELL</th><th>BILL_NAME</th><th></th></tr></thead><tbody>";
                    foreach (DataRow item in dt.Rows)
                    {
                        st += "<tr>";
                        st += "   <td>" + i.ToString() + "</td>";
                        st += "   <td>" + item["EDI_NO"].ToString() + "</td>";
                        st += "   <td>" + item["SERIAL_NO"].ToString() + "</td>";
                        st += "   <td>" + item["MODEL"].ToString() + "</td>";
                        st += "   <td>" + item["SUFFIX"].ToString() + "</td>";
                        st += "   <td>" + item["SELLIN_DATE"].ToString() + "</td>";
                        st += "   <td>" + item["SELLOUT_DATE"].ToString() + "</td>";
                        st += "   <td>" + item["VRF_FLAG"].ToString() + "</td>";
                        st += "   <td>" + item["CFM_FLAG"].ToString() + "</td>";
                        st += "   <td>" + item["CFM_USER"].ToString() + "</td>";
                        st += "   <td>" + item["user_cell"].ToString() + "</td>";
                        st += "   <td><input id='txtCmt" + item["EDI_NO"].ToString() + "' type='text' value='" + item["CFM_COMMENT"].ToString() + "' style='width:120px;'></td>";
                        st += "   <td>" + item["SHOP_CODE"].ToString() + "</td>";
                        st += "   <td>" + item["SHOP_NAME"].ToString() + "</td>";
                        st += "   <td>" + item["SHOP_CELL"].ToString() + "</td>";
                        st += "   <td>" + item["BILL_NAME"].ToString() + "</td>";
                        if (!string.IsNullOrEmpty(item["SERIAL_NO"].ToString()) && item["CFM_FLAG"].ToString().ToUpper() == "N")
                            st += "    <td><input type='button' id='btnSelect" + i.ToString() + "' value='Confirm' onclick=\"doConfirm('"
                                + total + "','" + i.ToString()
                                + "','" + item["SHOP_CODE"].ToString()
                                + "','" + item["SHOP_CELL"].ToString()
                                + "','" + item["EDI_NO"].ToString()
                                + "','" + item["SERIAL_NO"].ToString()
                                + "','" + item["MODEL"].ToString()
                                + "','" + item["SUFFIX"].ToString()
                                + "','" + item["SELLOUT_DATE"].ToString()
                                + "','" + item["SELLIN_DATE"].ToString()
                                + "','" + item["user_cell"].ToString()
                                + "')\" /><img id='imgSelect" + i.ToString() + "' src='../IMAGES/icons/fugue/tick-circle.png' style='display:none;' /></td>";
                        else
                            st += "    <td><input type='button' disabled='disabled' id='btnSelect" + i.ToString() + "' value='Confirm' onclick=\"doConfirmSo('" + total + "','" + i.ToString() + "','" + item["EDI_NO"].ToString() + "')\" /><img id='imgSelect" + i.ToString() + "' src='../IMAGES/icons/fugue/tick-circle.png' style='display:none;' /></td>";

                        st += "</tr>";
                        i++;
                    }
                    st += "</tbody></table>";
                }
                else
                {
                    st = "No data Found.";
                }
            }
            catch (Exception ee)
            {
                if (ee.InnerException != null)
                    return (ee.InnerException.Message);
                else
                    return (ee.Message);
            }
            return st;

        }

        [WebMethod]
        public string doConfirmSO(string p_shop_code, string p_shop_cell, string p_edi_no, string p_serial_no, string p_model,
            string p_suffix, string p_sellout_date, string p_sellin_date, string p_user_cell, string p_cfm_comment, string p_cfm_user)
        {
            try
            {
                string error = "";
                string stx = "select pk_som_mst.FN_SO_INCT_DEALER_SET_MNL_CFM('"
                    + p_shop_code + "','"
                    + p_shop_cell + "','"
                    + p_edi_no + "','"
                    + p_serial_no + "','"
                    + p_model + "','"
                    + p_suffix + "','"
                    + p_sellout_date + "','"
                    + p_sellin_date + "','"
                    + p_user_cell + "','"
                    + p_cfm_comment + "','"
                    + p_cfm_user + "') from dual";
                OracleCommand cmd = DBAction.con.CreateCommand();
                OracleParameter param = new OracleParameter();
                cmd.CommandText = stx;
                cmd.CommandType = CommandType.Text;
                var x = cmd.ExecuteScalar();
                error = x.ToString();
                return error;
            }
            catch (Exception ee)
            {
                return ee.Message;
            }
        }
    }

}
